/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package mpinventory;

import java.sql.*;
import java.util.List;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.swing.JOptionPane;
/**
 *
 * @author user
 */
public class SupplierQuery {
    private Connection conn;
    public Supplier supplier;
    private String searchBy;
    public SupplierQuery(Connection conn){
        this.conn=conn;
    };
    public boolean canAddSupplier(String supplierName, String supplierAddress){
        String query="select supplierName FROM supplierdata where supplierName = ? AND supplierAddress=?";
        try{
            PreparedStatement prest=conn.prepareStatement(query);
            prest.setString(1, supplierName);
            prest.setString(2, supplierAddress);
            ResultSet rs=prest.executeQuery();
            while(rs.next())
                return false;
        }
        catch(SQLException ex){
            Logger.getLogger(SupplierQuery.class.getName()).log(Level.SEVERE, null, ex);
        }
        return true;
    }
    public void addSupplier(int supplierID, String supplierName, String supplierAddress, String supplierContactNumber){
        String query="insert into supplierdata(supplierID, supplierName, supplierAddress, supplierContactNumber) values(?,?,?,?)";
        try{
            PreparedStatement prest=conn.prepareStatement(query);
            prest.setInt(1, supplierID);
            prest.setString(2, supplierName);
            prest.setString(3, supplierAddress);
            prest.setString(4, supplierContactNumber);
            prest.executeUpdate();
            JOptionPane.showMessageDialog(null, "Successfully added " + supplierName + " to the database!");
        }
        catch(SQLException ex){
            Logger.getLogger(SupplierQuery.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
    public List<Supplier> initializeSuppliers(){
        List<Supplier> suppliers=new ArrayList<Supplier>();
        try{
            Statement stmt=conn.createStatement();
            String query="select * from supplierdata ORDER BY supplierName ASC";
            ResultSet rs=stmt.executeQuery(query);
            while(rs.next()){
                Supplier supplier=new Supplier();
                supplier.setID(rs.getInt("supplierID"));
                supplier.setName(rs.getString("supplierName"));
                supplier.setAddress(rs.getString("supplierAddress"));
                supplier.setContactNumbers(rs.getString("supplierContactNumber"));
                suppliers.add(supplier);
            }
        }
        catch(SQLException ex){
            Logger.getLogger(SupplierQuery.class.getName()).log(Level.SEVERE, null, ex);
        }
        return suppliers;
    }
    public void editSupplier(int supplierID, String supplierName, String supplierAddress, String supplierContactNumber){
        String query="update supplierdata set supplierName=?, supplierAddress=?, supplierContactNumber=? where supplierID=?";
        try{
            PreparedStatement prest=conn.prepareStatement(query);
            prest.setString(1, supplierName);
            prest.setString(2, supplierAddress);
            prest.setString(3, supplierContactNumber);
            prest.setInt(4, supplierID);
            prest.executeUpdate();
            JOptionPane.showMessageDialog(null, "Successfully edited " + supplierName + "!");
        }
        catch(SQLException ex){
            Logger.getLogger(SupplierQuery.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
    public void interpretSearchBy(int n){
        if(n==0)
            searchBy="All";
        else if(n==1)
            searchBy="Name";
        else if(n==2)
            searchBy="Address";
        else if(n==3)
            searchBy="Contact Number/s";
    }
    public List<Supplier> search(String text){
        List<Supplier> suppliers=new ArrayList<Supplier>();
        String query;
        if(searchBy.equals("Name"))
            query="select * FROM supplierdata where supplierName like ? ORDER BY supplierName ASC";
        else if(searchBy.equals("Address"))
            query="select * FROM supplierdata where supplierAddress like ? ORDER BY supplierName ASC";
        else
            query="select * FROM supplierdata where supplierContactNumber like ? ORDER BY supplierName ASC";
        try{
            PreparedStatement prest=conn.prepareStatement(query);
            prest.setString(1, "%"+text+"%");
            ResultSet rs=prest.executeQuery();
            while(rs.next()){
                Supplier supplier=new Supplier();
                supplier.setID(rs.getInt("supplierID"));
                supplier.setName(rs.getString("supplierName"));
                supplier.setAddress(rs.getString("supplierAddress"));
                supplier.setContactNumbers(rs.getString("supplierContactNumber"));
                suppliers.add(supplier);
            }
        }
        catch(SQLException ex){
            Logger.getLogger(SupplierQuery.class.getName()).log(Level.SEVERE, null, ex);
        }
        if(suppliers.isEmpty()){
            JOptionPane.showMessageDialog(null, "The keyword that you have just typed in does not belong to any supplier or falls under the wrong category. Please check your input.");
            return null;
        }
        return suppliers;
    }
    public int setID(){
        try{
            Statement stmt=conn.createStatement();
            String query="select max(supplierID) as maxID from supplierdata";
            ResultSet rs=stmt.executeQuery(query);
            while(rs.next())
                return rs.getInt("maxID")+1;
        }
        catch(SQLException ex){
            Logger.getLogger(SupplierQuery.class.getName()).log(Level.SEVERE, null, ex);
        }
        return 0;
    }
}
